/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package lomtools.persistence;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;

/**
 *
 * @author Leonardo Oliveira Moreira
 * 
 */
public final class DAOUtils {

    private DAOUtils() {
        
    }

    public static final String getSelectSequence(String sequenceName) {
        String sql = "";
        if (sequenceName == null || sequenceName.trim().length() == 0) {
            return null;
        }
        sql += "SELECT nextval('" + sequenceName + "') AS sequence";
        return sql;
    }

    public static final String getSelect(Bean bean) throws SQLException {
        String sql = "";
        sql += "SELECT ";
        Field[] declaredFields = bean.getClass().getDeclaredFields();
        int columnCount = 0;
        for (Field f : declaredFields) {
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    sql += c.name() + ", ";
                    columnCount++;
                }
            }
        }
        if (columnCount == 0)
            throw new SQLException("No column found");
        if (sql.endsWith(", ")) {
            sql = sql.substring(0, sql.length() - ", ".length());
        }
        sql += " FROM ";
        Annotation[] annotations = bean.getClass().getAnnotations();
        boolean tableFound = false;
        for (Annotation a : annotations) {
            if (a.annotationType() == javax.persistence.Table.class) {
                javax.persistence.Table t = (javax.persistence.Table) a;
                sql += t.name();
                tableFound = true;
            }
        }
        if (! tableFound)
            throw new SQLException("No table found");
        sql += " ";
        sql += "WHERE";
        sql += " ";
        for (Field f : declaredFields) {
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    Object value = getValue(bean, f);
                    if (f.getType() == java.lang.Integer.class|| f.getType() == java.lang.Long.class || f.getType() == java.lang.Double.class) {
                        String tempValue = (value == null ? "-1" : "" + value.toString() + "");
                        sql += "(-1 = " +  tempValue + " OR " + c.name() + " = " + tempValue + ")" + " AND ";
                        continue;
                    }
                    if (f.getType() == java.util.Date.class) {
                        continue;
                    }
                    String tempValue = (value == null ? "%" : "%" + value.toString().toUpperCase() + "%");
                    sql += "(UPPER(" + c.name() + ") LIKE '" + tempValue + "')" + " AND ";
                }
            }
        }
        if (sql.endsWith(" AND ")) {
            sql = sql.substring(0, sql.length() - " AND ".length());
        }
        return sql;
    }

    public static final String getSelectId(Bean bean) throws SQLException {
        String sql = "";
        sql += "SELECT ";
        Field[] declaredFields = bean.getClass().getDeclaredFields();
        int columnCount = 0;
        for (Field f : declaredFields) {
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    sql += c.name() + ", ";
                    columnCount++;
                }
            }
        }
        if (columnCount == 0)
            throw new SQLException("No column found");
        if (sql.endsWith(", ")) {
            sql = sql.substring(0, sql.length() - ", ".length());
        }
        sql += " FROM ";
        Annotation[] annotations = bean.getClass().getAnnotations();
        boolean tableFound = false;
        for (Annotation a : annotations) {
            if (a.annotationType() == javax.persistence.Table.class) {
                javax.persistence.Table t = (javax.persistence.Table) a;
                sql += t.name();
                tableFound = true;
            }
        }
        if (! tableFound)
            throw new SQLException("No table found");
        sql += " ";
        sql += "WHERE";
        sql += " ";
        columnCount = 0;
        for (Field f : declaredFields) {
            boolean fieldId = false;
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Id.class) {
                    fieldId = true;
                }
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    if (fieldId) {
                        Object value = getValue(bean, f);
                        sql += c.name() + " = " + (value == null ? "NULL" : "'" + value.toString() + "'") + " AND ";
                        columnCount++;
                    }
                }
            }
        }
        if (columnCount == 0)
            throw new SQLException("No column found");
        if (sql.endsWith(" AND ")) {
            sql = sql.substring(0, sql.length() - " AND ".length());
        }
        return sql;
    }

    public static final String getInsert(Bean bean) throws SQLException {
        String sql = "";
        sql += "INSERT INTO ";
        Annotation[] annotations = bean.getClass().getAnnotations();
        boolean tableFound = false;
        for (Annotation a : annotations) {
            if (a.annotationType() == javax.persistence.Table.class) {
                javax.persistence.Table t = (javax.persistence.Table) a;
                sql += t.name();
                tableFound = true;
            }
        }
        if (! tableFound)
            throw new SQLException("No table found");
        sql += " ";
        sql += "(";
        String values = "";
        Field[] declaredFields = bean.getClass().getDeclaredFields();
        int columnCount = 0;
        for (Field f : declaredFields) {
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    Object value = getValue(bean, f);
                    if (! c.nullable() && value == null) {
                        throw new SQLException(f.getName() + " is null");
                    }
                    sql += c.name() + ", ";
                    values += "" + (value == null ? "NULL" : "'" + value.toString() + "'") + ", ";
                    columnCount++;
                }
            }
        }
        if (columnCount == 0)
            throw new SQLException("No column found");
        if (sql.endsWith(", ")) {
            sql = sql.substring(0, sql.length() - ", ".length());
        }
        if (values.endsWith(", ")) {
            values = values.substring(0, values.length() - ", ".length());
        }
        sql += ")";
        sql += " VALUES ";
        sql += "(";
        sql += values;
        sql += ")";
        return sql;
    }

    public static final String getUpdate(Bean bean) throws SQLException {
        String sql = "";
        sql += "UPDATE ";
        Annotation[] annotations = bean.getClass().getAnnotations();
        boolean tableFound = false;
        for (Annotation a : annotations) {
            if (a.annotationType() == javax.persistence.Table.class) {
                javax.persistence.Table t = (javax.persistence.Table) a;
                sql += t.name();
                tableFound = true;
            }
        }
        if (! tableFound)
            throw new SQLException("No table found");
        sql += " ";
        sql += "SET";
        sql += " ";
        Field[] declaredFields = bean.getClass().getDeclaredFields();
        int columnCount = 0;
        for (Field f : declaredFields) {
            boolean fieldId = false;
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Id.class) {
                    fieldId = true;
                }
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    if (! fieldId) {
                        Object value = getValue(bean, f);
                        sql += c.name() + " = " + (value == null ? "NULL" : "'" + value.toString() + "'") + ", ";
                        columnCount++;
                    }
                }
            }
        }
        if (columnCount == 0)
            throw new SQLException("No column found");
        if (sql.endsWith(", ")) {
            sql = sql.substring(0, sql.length() - ", ".length());
        }
        sql += " ";
        sql += "WHERE";
        sql += " ";
        columnCount = 0;
        for (Field f : declaredFields) {
            boolean fieldId = false;
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Id.class) {
                    fieldId = true;
                }
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    if (fieldId) {
                        Object value = getValue(bean, f);
                        sql += c.name() + " = " + (value == null ? "NULL" : "'" + value.toString() + "'") + " AND ";
                        columnCount++;
                    }
                }
            }
        }
        if (columnCount == 0)
            throw new SQLException("No column found");
        if (sql.endsWith(" AND ")) {
            sql = sql.substring(0, sql.length() - " AND ".length());
        }
        return sql;
    }

    public static final String getDelete(Bean bean) throws SQLException {
        String sql = "";
        sql += "DELETE FROM ";
        Annotation[] annotations = bean.getClass().getAnnotations();
        boolean tableFound = false;
        for (Annotation a : annotations) {
            if (a.annotationType() == javax.persistence.Table.class) {
                javax.persistence.Table t = (javax.persistence.Table) a;
                sql += t.name();
                tableFound = true;
            }
        }
        if (! tableFound)
            throw new SQLException("No table found");
        sql += " ";
        sql += "WHERE";
        sql += " ";
        Field[] declaredFields = bean.getClass().getDeclaredFields();
        int columnCount = 0;
        for (Field f : declaredFields) {
            boolean fieldId = false;
            Annotation[] declaredAnnotations = f.getDeclaredAnnotations();
            for (Annotation a : declaredAnnotations) {
                if (a.annotationType() == javax.persistence.Id.class) {
                    fieldId = true;
                }
                if (a.annotationType() == javax.persistence.Column.class) {
                    javax.persistence.Column c = (javax.persistence.Column) a;
                    if (fieldId) {
                        Object value = getValue(bean, f);
                        sql += c.name() + " = " + (value == null ? "NULL" : "'" + value.toString().toUpperCase() + "'") + " AND ";
                        columnCount++;
                    }
                }
            }
        }
        if (columnCount == 0)
            throw new SQLException("No column found");
        if (sql.endsWith(" AND ")) {
            sql = sql.substring(0, sql.length() - " AND ".length());
        }
        return sql;
    }

    public static final Object getValue(Bean bean, Field f) {
        Method[] methods = bean.getClass().getMethods();
        for (Method m : methods) {
            if (m.getName().startsWith("get")) {
                if (m.getName().contains("get" + f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1))) {
                    try {
                        return m.invoke(bean);
                    } catch (IllegalAccessException ex) {
                        return null;
                    } catch (IllegalArgumentException ex) {
                        return null;
                    } catch (InvocationTargetException ex) {
                        return null;
                    }
                }
            }
        }
        return null;
    }
    
}
